package com.comparyinfo.util;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.*;

/**
 * jdbc工具类
 */
public class JdbcUtil {

    private static String  driverClassName;
    private static String url;
    private static String userName;
    private static String password;

    static {
        InputStream resourceAsStream = JdbcUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();

        try {
            properties.load(resourceAsStream);
            driverClassName = properties.getProperty("driverClassName");
            url = properties.getProperty("url");
            userName = properties.getProperty("userName");
            password = properties.getProperty("password");
            //System.out.println(driverClassName + url + userName + password);

            Class.forName(driverClassName);
        } catch (IOException | ClassNotFoundException e) {
            e.printStackTrace();
        }finally {
            try {
                resourceAsStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    public static Connection getConnection() {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(url,userName,password);
            return connection;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return connection;
    }

    public static void main(String[] args) {
        getConnection();
    }

    public static void closeAll(Connection connection, Statement statement, PreparedStatement preparedStatement, ResultSet resultSet) {
        if(connection != null) {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if(statement != null) {
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if(preparedStatement != null) {
            try {
                preparedStatement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if(resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    /**
     * 增删改
     *
     * @param sql    预编译SQL语句
     * @param params 参数
     * @return 受影响的记录数目
     */
    public static int executeUpdate(String sql, List<Object> params) {
        int result = -1;
        if (sql == null || sql.isEmpty()) {
            return result;
        }
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            connection = getConnection();
            ps = connection.prepareStatement(sql);
            if (params != null) {
                for (int i = 0; i < params.size(); i++) {
                    ps.setObject(i + 1, params.get(i));
                }
            }
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            release(ps, connection);
        }
        return result;  // 更新数据失败
    }

    /**
     * 查
     *
     * @param sql    预编译SQL语句
     * @param params 参数
     */
    public static ResultSet executeQuery(String sql, List<Object> params,Connection connection) {
        if (sql == null || sql.isEmpty()) {
            return null;
        }
        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement(sql);
            if (params != null) {
                for (int i = 0; i < params.size(); i++) {
                    ps.setObject(i + 1, params.get(i));
                }
            }
            ResultSet resultSet = ps.executeQuery();
            return resultSet;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //release(null, connection);
        }
        return null;  // 更新数据失败
    }

    /**
     * 查
     *
     * @param sql    预编译SQL语句
     * @param params 参数
     */
    public static <T> List<T> executeQuery(String sql, List<Object> params, Class<T> tClass) {
        Connection connection = getConnection();
        List<T> ts = new ArrayList<>();
        if (sql == null || sql.isEmpty()) {
            return ts;
        }
        ResultSet rs = executeQuery(sql, params,connection);
        if (rs == null) {
            return ts;
        }
        List<Field> list = getAllFieldList(tClass);
        Map<String, Field> fieldMap = toMap(list, field -> field.getName());
        //for (Field field : list) {
        //  Column annotation = field.getAnnotation(Column.class);
        //  if (annotation == null || StringUtils.isEmpty(annotation.name())) {
        //      continue;
        //  }
        //  String camelCase = StringUtils.getLowerCamelCase(annotation.name());
        //  fieldMap.put(camelCase, field);
        //}
        // 获取数据库表结构
        ResultSetMetaData meta;
        try {
            meta = rs.getMetaData();
            while (rs.next()) {
                try {
                    T t = tClass.newInstance();
                    // 循环获取指定行的每一列的信息
                    for (int i = 1; i <= meta.getColumnCount(); i++) {
                        // 当前列名
                        String colName = meta.getColumnLabel(i);
                        colName = getLowerCamelCase(colName);

                        // 获取当前位置的值，返回Object类型
                        Field field = fieldMap.get(colName);
                        Object val = rs.getObject(i);
                        setData(t, field, val);
                    }
                    ts.add(t);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            release(null,connection);
        }

        return ts;  // 更新数据失败
    }

    public static List<Field> getAllFieldList(Class<?> tClass) {
        return getFieldList(tClass, true, null);
    }

    public static List<Field> getFieldList(Class<?> tClass, boolean superClass, List<Field> fieldSet) {
        if (fieldSet == null) {
            fieldSet = new ArrayList<>();
        }
        if (tClass == null) {
            return fieldSet;
        }
        fieldSet.addAll(Arrays.asList(tClass.getFields()));
        fieldSet.addAll(Arrays.asList(tClass.getDeclaredFields()));

        if (superClass) {
            Class supperClass = tClass.getSuperclass();
            if (!Object.class.equals(supperClass)) {
                return getFieldList(supperClass, superClass, fieldSet);
            }
        }
        fieldSet = new ArrayList<>(new HashSet<>(fieldSet));//去重
        return fieldSet;
    }

    private static <T> void setData(T t, Field field, Object val) {
        boolean temp = field.isAccessible();
        field.setAccessible(true);
        try {
            field.set(t, val);
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }
        field.setAccessible(temp);
    }

    public static String getLowerCamelCase(String name) {
        name = getUpperCamelCase(name);
        String name1=name.substring(0, 1).toLowerCase() + name.substring(1);
        return name.substring(0, 1).toLowerCase() + name.substring(1);
    }

    public static String getUpperCamelCase(String name) {
        if (name == null || "".equals(name)) {
            return "";
        }
        String[] strings = name.split("[^a-zA-Z0-9]+");
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < strings.length; i++) {
            sb.append(name.substring(0, 1).toUpperCase()).append(name.substring(1).toLowerCase());
        }
        return sb.toString();
    }


    public static void release(Statement stmt, Connection conn) {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static <K, T> Map<K, T> toMap(Collection<T> collection, Callback<K, T> callback) {
        Map<K, T> map = new HashMap<>();
        if (collection == null) {
            return map;
        }
        for (T t : collection) {
            K k = callback.call(t);
            map.put(k, t);
        }
        return map;
    }

    interface Callback<K, T> {
        K call(T t);
    }

}
